package spirit;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class J03_MemberDAO implements J03_IMemberDAO {
	
	private String DRV ="oracle.jdbc.driver.OracleDriver";
	private String URL ="jdbc:oracle:thin:spirit/spirit@112.216.253.125:1521:xe";
	private String insertSQL = "insert into j03MEMBER values(exam.nextval,?,?,?,?,sysdate)";
	private String selectSQL = " select * from J03Member order by mno desc";
    private String updateSQL = "update J03Member set passwd = ?, name = ?, email = ?, regdate=sysdate where mno = ?";
    private String deleteSQL = "delete J03Member where mno = ?";
    
	@Override
	public boolean insertMember(J03_MemberVO m) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try{
			conn = this.openDatebase();
			pstmt = conn.prepareStatement(insertSQL);
			// ? 매개변수에 대입할 값 지정
			pstmt.setString(1, m.getUserid());
			pstmt.setString(2, m.getPasswd());
			pstmt.setString(3, m.getName());
			pstmt.setString(4, m.getEmail());
			
			pstmt.executeUpdate();
			result = true;
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			closeDatebase(conn,pstmt,null);
		}
		return result;
	}
		//JDBC Conenction 객체를 생성하는 메서드
			private Connection openDatebase(){
			try{
		
			Class.forName(DRV);
			return DriverManager.getConnection(URL);
			}catch(Exception ex){
				ex.printStackTrace();
			}
				return null;
			
			}
	private void closeDatebase(
			
			Connection c, PreparedStatement p, ResultSet r){
		if(r != null)try{r.close(); r = null;}catch(Exception e){}
		if(p != null)try{p.close(); p = null;}catch(Exception e){}
		if(c != null)try{c.close(); c = null;}catch(Exception e){}
	}
	@Override
	// select 문으로 전체 회원정보를 조회한 후
	// MemberVO를 내용으로 가지는 
	// 동적배열 List로 결과를 넘김
	public List<J03_MemberVO> selectMember() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<J03_MemberVO> result = new ArrayList<>();
		try{
		 conn = openDatebase();
		 pstmt = conn.prepareStatement(selectSQL);
		 rs = pstmt.executeQuery();
		 while(rs.next()){
			 J03_MemberVO m = new J03_MemberVO();
			 m.setMno(rs.getString("mno"));
			 m.setUserid(rs.getString("userid"));
			 m.setPasswd(rs.getString("passwd"));
			 m.setName(rs.getString("name"));
			 m.setEmail(rs.getString("email"));
			 m.setRegdate(rs.getString("regdate"));
			 
			 result.add(m);
		 }
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
		 closeDatebase(conn,pstmt,rs);
		}
		return result;
	}
	@Override
	public boolean updateMember(J03_MemberVO m) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		try{
			conn = openDatebase();
			pstmt = conn.prepareStatement(updateSQL);
			pstmt.setString(1, m.getPasswd());
			pstmt.setString(2, m.getName());
			pstmt.setString(3, m.getEmail());
			pstmt.setString(4, m.getMno());
			int cnt = pstmt.executeUpdate();
			if(cnt == 0)
		     System.out.println("명령은 잘 실행됐지만,수정된 데이터는 없음");
			result = true;
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			closeDatebase(conn,pstmt,null);
		}
		return result;
	}
	@Override
	public boolean deleteMember(J03_MemberVO m) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		try{
			conn = openDatebase();
			pstmt = conn.prepareStatement(deleteSQL);
			pstmt.setString(1,m.getMno());
			int cnt = pstmt.executeUpdate();
			if(cnt == 0)
		     System.out.println("명령은 잘 실행됐지만, 삭제된데이터는없음");
			result = true;
		}catch(Exception ex){
			ex.printStackTrace();		
		}finally{
			closeDatebase(conn,pstmt,null);
		}
		return result;
	}
	
}